Attribute VB_Name = "CreaFiglio"
Option Explicit
Function CreateDB(ByVal NomeDB As String, ByRef conn As ADODB.Connection) As Boolean
    'If Not QueryComando("exec dbo.spcreatedb @dbname='" & NomeDB & "'", conn) Then Exit Function
    If Not QueryComando("spcreatedb", conn, , Array(NomeDB)) Then Exit Function
    If Not CreateTbl(NomeDB, conn) Then Exit Function
    CreateDB = True
End Function
Function CreateTbl(ByVal DBname As String, ByRef conn As ADODB.Connection) As Boolean
    'crea TUTTE le tabelle del database figlio
    Dim stringa As String
    stringa = "CREATE TABLE " & _
        DBname & ".[dbo].[TblCartolina] ( " & _
        "[IDCartolina] [int] NOT NULL ," & _
        "[ValorePrezzo] [Money] NOT NULL , " & _
        "[ValoreCondizione] [int] NOT NULL , " & _
        "[DescrizioneCondizione] [text] NOT NULL , " & _
        "[IDNazione] [int] NULL , " & _
        "[IDProvincia] [int] NULL , " & _
        "[Numero] [text] COLLATE Latin1_General_CI_AS NULL , " & _
        "[Editore] [text] COLLATE Latin1_General_CI_AS NULL , " & _
        "[FLViaggiata] [bit] NOT NULL , " & _
        "[DataSpedizione] [datetime] NULL ," & _
        "[Luogo] [text] COLLATE Latin1_General_CI_AS NULL " & _
        ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] "
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "CREATE TABLE " & DBname & ".[dbo].[TblCartolinaDifetto] (" & _
        "[IDCartolina] [int] NOT NULL , " & _
        "[IDDifetto]  [Int] NOT NULL " & _
        ") ON [PRIMARY] "
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "CREATE TABLE " & DBname & ".[dbo].[TblCategoria] (" & _
        "[IDUltimoTipo] [char] (10) COLLATE Latin1_General_CI_AS NULL , " & _
        "[IDCategoria] [int] NOT NULL ," & _
        "[NomeCategoria] [text] COLLATE Latin1_General_CI_AS NOT NULL" & _
        ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "CREATE TABLE " & DBname & ".[dbo].[TblDifetto] (" & _
        "[IDDifetto] [int] NOT NULL ," & _
        "[DescrizioneDifettoITA] [text] COLLATE Latin1_General_CI_AS NOT NULL ," & _
        "[DescrizioneDifettoENG] [text] COLLATE Latin1_General_CI_AS NOT NULL" & _
        ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "CREATE TABLE " & DBname & ".[dbo].[TblNazione] (" & _
        "[IDNazione] [int] NOT NULL ," & _
        "[IDUltimoFiglio] [int] NULL ," & _
        "[NomeNazione] [text] COLLATE Latin1_General_CI_AS NOT NULL" & _
        ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "CREATE TABLE " & DBname & ".[dbo].[TblProvincia] (" & _
        "[IDNazione] [int] NOT NULL ," & _
        "[IDProvincia] [int] NOT NULL , " & _
        "[NomeProvincia] [text] COLLATE Latin1_General_CI_AS NOT NULL" & _
        ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "CREATE TABLE " & DBname & ".[dbo].[TblTematica] (" & _
        "[IDCartolina] [int] NOT NULL ," & _
        "[IDCategoria] [int] NOT NULL , " & _
        "[IDTipo] [int] NOT NULL , " & _
        "[Sottotipo] [text] COLLATE Latin1_General_CI_AS NULL" & _
        ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "CREATE TABLE " & DBname & ".[dbo].[TblTipo] (" & _
        "[IDCategoria] [int] NOT NULL ," & _
        "[IDTipo] [int] NOT NULL ," & _
        "[Nometipo] [text] COLLATE Latin1_General_CI_AS NOT NULL" & _
        ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblCartolina] WITH NOCHECK ADD " & _
        "CONSTRAINT [PK_TblCartolina] PRIMARY KEY  NONCLUSTERED" & _
        "(  [IDCartolina] )  ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblCartolinaDifetto] WITH NOCHECK ADD " & _
        "CONSTRAINT [PK_TblCartolinaDifetto] PRIMARY KEY  NONCLUSTERED" & _
        "( [IDCartolina], [IDDifetto] )  ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblCategoria] WITH NOCHECK ADD " & _
        "CONSTRAINT [PK_TblCategoria] PRIMARY KEY  NONCLUSTERED" & _
        "( [IDCategoria] )  ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblDifetto] WITH NOCHECK ADD " & _
        "CONSTRAINT [PK_TblDifetto] PRIMARY KEY  NONCLUSTERED" & _
        "( [IDDifetto]  )  ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblNazione] WITH NOCHECK ADD " & _
        "CONSTRAINT [PK_TblNazione] PRIMARY KEY  NONCLUSTERED" & _
        "( [IDNazione]  )  ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblProvincia] WITH NOCHECK ADD " & _
        "CONSTRAINT [PK_TblProvincia] PRIMARY KEY  NONCLUSTERED" & _
        "( [IDNazione], [IDProvincia] )  ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblTematica] WITH NOCHECK ADD " & _
        "CONSTRAINT [PK_TblTematica] PRIMARY KEY  NONCLUSTERED" & _
        "( [IDCartolina], [IDCategoria], [IDTipo] )  ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblTipo] WITH NOCHECK ADD " & _
        "CONSTRAINT [PK_TblTipo] PRIMARY KEY  NONCLUSTERED" & _
        "( [IDCategoria],[IDTipo])  ON [PRIMARY]"
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblCartolina] ADD " & _
        "CONSTRAINT [FK_TblCartolina_TblProvincia] FOREIGN KEY " & _
        "( [IDNazione], [IDProvincia] ) REFERENCES [TblProvincia] ( [IDNazione], [IDProvincia] ), " & _
        "CONSTRAINT [FK_TblCartolina_TblProvincia1] FOREIGN KEY " & _
        "( [IDNazione],[IDProvincia]) REFERENCES [TblProvincia] ([IDNazione], [IDProvincia] ) "
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "alter table " & DBname & ".[dbo].[TblCartolina] nocheck constraint [FK_TblCartolina_TblProvincia] "
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "alter table " & DBname & ".[dbo].[TblCartolina] nocheck constraint [FK_TblCartolina_TblProvincia1] "
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblCartolinaDifetto] ADD " & _
        "CONSTRAINT [FK_TblCartolinaDifetto_TblCartolina] FOREIGN KEY " & _
        "([IDCartolina]) REFERENCES [TblCartolina] ([IDCartolina]), " & _
        "CONSTRAINT [FK_TblCartolinaDifetto_TblDifetto] FOREIGN KEY " & _
        "( [IDDifetto]) REFERENCES [TblDifetto] ([IDDifetto]) "
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblProvincia] ADD " & _
        "CONSTRAINT [FK_TblProvincia_TblNazione] FOREIGN KEY " & _
        "([IDNazione]) REFERENCES [TblNazione] ( [IDNazione]  ) "
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblTematica] ADD " & _
        "CONSTRAINT [FK_TblTematica_TblCartolina] FOREIGN KEY " & _
        "( [IDCartolina]) REFERENCES [TblCartolina] ([IDCartolina]), " & _
        "CONSTRAINT [FK_TblTematica_TblTipo] FOREIGN KEY " & _
        "( [IDCategoria], [IDTipo] ) REFERENCES [TblTipo] ( [IDCategoria],[IDTipo]), " & _
        "CONSTRAINT [FK_TblTematica_TblTipo1] FOREIGN KEY " & _
        "([IDCategoria],[IDTipo] ) REFERENCES [TblTipo] ( [IDCategoria], [IDTipo] ) "
    If Not QueryComando(stringa, conn) Then Exit Function
    stringa = "ALTER TABLE " & DBname & ".[dbo].[TblTipo] ADD " & _
        "CONSTRAINT [FK_TblTipo_TblCategoria] FOREIGN KEY " & _
        "([IDCategoria]) REFERENCES [TblCategoria] ([IDCategoria]) "
    If Not QueryComando(stringa, conn) Then Exit Function
    If Not CreateValue(DBname, conn) Then Exit Function
    CreateTbl = True
End Function
Function CreateValue(ByVal DBname As String, ByRef conn As ADODB.Connection) As Boolean
    'popola le tabelle
    Dim rst As ADODB.Recordset
    Dim rst2 As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim NumCartoline As Long
    Dim ID As Long
    Dim SQL As String
    rst.CursorType = adOpenStatic
    If Not QueryComando("viewdifettopresente", conn, rst) Then GoTo Esci
    Do Until rst.EOF
        ID = rst.Fields(0)
        NumCartoline = rst.Fields(1)
        If NumCartoline > 0 Then
            Set rst2 = New ADODB.Recordset
            rst2.CursorType = adOpenStatic
            'inserisci il difetto in tbldifetto
            If Not QueryComando("sceglidifetto", conn, rst2, Array(ID)) Then GoTo Esci
            SQL = "INSERT INTO " & DBname & ".[dbo].tbldifetto (iddifetto, descrizionedifettoita, descrizionedifettoeng) " & _
                "VALUES (' " & rst2.Fields(0) & " ',' " & rst2.Fields(1) & " ',' " & rst2.Fields(2) & " ')"
            If Not QueryComando(SQL, conn) Then GoTo Esci
        End If
        rst.MoveNext
    Loop
    Set rst = New ADODB.Recordset
    rst.CursorType = adOpenStatic
    If Not QueryComando("viewtipopresente", conn, rst) Then GoTo Esci
    Do Until rst.EOF
        ID = rst.Fields(0)
        NumCartoline = rst.Fields(1)
        If NumCartoline > 0 Then
            Set rst2 = New ADODB.Recordset
            rst2.CursorType = adOpenStatic
            'inserisci in tblcategoria e tbltipo
            If Not QueryComando("ScegliTipo", conn, rst2, Array(ID)) Then Exit Function
            SQL = "INSERT INTO " & DBname & ".[dbo].tblcategoria (idcategoria, nomecategoria) " & _
                "VALUES (' " & rst2.Fields(1) & " ',' " & rst2.Fields(3) & " ')"
            If Not QueryComando(SQL, conn) Then GoTo Esci
            SQL = "INSERT INTO " & DBname & ".[dbo].tbltipo (idtipo, idcategoria, nometipo) " & _
                "VALUES (' " & rst2.Fields(0) & " ',' " & rst2.Fields(1) & " ',' " & rst2.Fields(2) & " ')"
            If Not QueryComando(SQL, conn) Then GoTo Esci
        End If
        rst.MoveNext
    Loop
    Set rst = New ADODB.Recordset
    rst.CursorType = adOpenStatic
    If Not QueryComando("ViewProvinciaPresente", conn, rst) Then GoTo Esci
    Do Until rst.EOF
        ID = rst.Fields(0)
        NumCartoline = rst.Fields(1)
        If NumCartoline > 0 Then
            Set rst2 = New ADODB.Recordset
            rst2.CursorType = adOpenStatic
            'riempi tblnazione e tblprovincia
            If Not QueryComando("ScegliProvincia", conn, rst2, Array(ID)) Then GoTo Esci
            SQL = "INSERT INTO " & DBname & ".[dbo].tblnazione (idnazione, nomenazione) " & _
                "VALUES (' " & rst2.Fields(1) & " ',' " & rst2.Fields(3) & " ')"
            If Not QueryComando(SQL, conn) Then GoTo Esci
            SQL = "INSERT INTO " & DBname & ".[dbo].tblprovincia (idprovincia, idnazione, nomeprovincia) " & _
                "VALUES (' " & rst2.Fields(0) & " ',' " & rst2.Fields(1) & " ',' " & rst2.Fields(2) & " ')"
            If Not QueryComando(SQL, conn) Then GoTo Esci
        End If
        rst.MoveNext
    Loop
    'riempi tblcartolina
    Set rst = New ADODB.Recordset
    rst.CursorType = adOpenStatic
    If Not QueryComando("viewSelezionate", conn, rst) Then GoTo Esci
    Do Until rst.EOF
        SQL = "SET DATEFORMAT dmy INSERT INTO " & DBname & ".[dbo].tblcartolina (idcartolina, ValorePrezzo, ValoreCondizione, DescrizioneCondizione, IDNazione, IDProvincia, Numero, Editore, FLViaggiata, DataSpedizione, Luogo) " & _
            "Values (' " & rst.Fields(0) & "', Cast (" & rst.Fields(1) & " AS money ),'" & rst.Fields(2) & "','" & rst.Fields(3) & "','" & rst.Fields(4) & "','" & rst.Fields(5) & "','" & rst.Fields(6) & "','" & rst.Fields(7) & "', cast( " & CInt(rst.Fields(8)) & "  as bit), cast( '" & CDate(rst.Fields(9)) & "' as datetime),'" & rst.Fields(10) & "')"
        If Not QueryComando(SQL, conn) Then GoTo Esci
        rst.MoveNext
    Loop
    'riempi tblcartolinadifetto
    Set rst = New ADODB.Recordset
    rst.CursorType = adOpenStatic
    If Not QueryComando("ViewDifettoSelezionate", conn, rst) Then GoTo Esci
    Do Until rst.EOF
        SQL = "INSERT INTO " & DBname & ".[dbo].tblcartolinadifetto (idcartolina, iddifetto) " & _
            "VALUES ('" & rst.Fields(0) & "','" & rst.Fields(1) & "')"
        If Not QueryComando(SQL, conn) Then GoTo Esci
        rst.MoveNext
    Loop
    'riempi tbltematica
    Set rst = New ADODB.Recordset
    rst.CursorType = adOpenStatic
    If Not QueryComando("ViewTematicheSelezionate", conn, rst) Then GoTo Esci
    Do Until rst.EOF
        SQL = "INSERT INTO " & DBname & ".[dbo].tbltematica (idcartolina, idcategoria, idtipo, sottotipo) " & _
            "VALUES ('" & rst.Fields(0) & "','" & rst.Fields(1) & "','" & rst.Fields(2) & "','" & rst.Fields(3) & "')"
        If Not QueryComando(SQL, conn) Then GoTo Esci
        rst.MoveNext
    Loop
    CreateValue = True
    Set rst = Nothing
    Set rst2 = Nothing
    Exit Function
Esci:
    CreateValue = False
    Set rst = Nothing
    Set rst2 = Nothing
End Function
